﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Web_Oto.Bases;
using Web_Oto.Common;
using Web_Oto.Data.Dto;

namespace Web_Oto.Data
{
    public class ProductDal: dalObject
    {
        #region constructors
        public ProductDal(string connectionstring, string connectiontype)
            : base(connectionstring, connectiontype)
        {
            
        }

        public ProductDal(IDbConnection sqlConnection, string connectiontype, SqlTransaction dbTransaction)
            : base(sqlConnection, connectiontype, dbTransaction)
        {

        }
        #endregion

        #region methods
        // default of current page always =1
        public IList<ProductDto> GetAllProduct(int? currentpage, int? itemsperpage, out int totalRecord)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var query= db.Products.ToList();
                totalRecord = query.Count;
                if (currentpage != null && itemsperpage != null)
                    query = query.Skip((Convert.ToInt32(currentpage) - 1)*Convert.ToInt32(itemsperpage))
                        .Take((Convert.ToInt32(currentpage))*Convert.ToInt32(itemsperpage))
                        .ToList();
                return query.Select(p => new ProductDto
                                             {
                                                 Id = p.Id,
                                                 //Code = p.Code,
                                                 Name = p.Name,
                                                 Price = p.Price,
                                                 Image = p.Image,
                                                 Hotline = p.Hotline,
                                                 TitleOfImage = p.TitleOfImage,
                                                 Description = p.Description,
                                                 CategoryId = p.CategoryID,
                                                 CategoryName = p.Category.Name,
                                                 CategoryParentId = p.Category.ParentId,
                                                 CategoryParentName = GetCategory(p.CategoryID, db),
                                                 ShowInConsultServices = p.ShowInConsultServices,
                                                 ShowInHomePage = p.ShowInHomePage,
                                                 ShowInNews = p.ShowInNews,
                                                 ShowInPayment = p.ShowInPayment,
                                                 SEO_Title = p.SEO_Title,
                                                 SEO_Keyword = p.SEO_Keyword,
                                                 SEO_Description = p.SEO_Description
                                             }).ToList();
            }
        }

        public IList<ProductDto> GetProducts(int? categoryId, int? parentId, int? currentpage, int? itemsperpage, out int totalRecord)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                if (categoryId != null && parentId != null)
                {
                    var query = db.Products.Where(p => p.Category.Id == categoryId && p.Category.ParentId == parentId).ToList();
                    totalRecord = query.Count;
                    if (currentpage != null && itemsperpage != null)
                        query = query.Skip((Convert.ToInt32(currentpage) - 1) * Convert.ToInt32(itemsperpage))
                            .Take(Convert.ToInt32(currentpage) * Convert.ToInt32(itemsperpage))
                            .ToList();
                    return query.Select(p => new ProductDto
                    {
                        Id = p.Id,
                        //Code = p.Code,
                        Name = p.Name,
                        Price = p.Price,
                        Hotline = p.Hotline,
                        TitleOfImage = p.TitleOfImage,
                        Image = p.Image,
                        Description = p.Description,
                        CategoryId = p.CategoryID,
                        CategoryName = p.Category.Name,
                        CategoryParentId = p.Category.ParentId,
                        CategoryParentName = GetCategory(p.CategoryID, db),
                        ShowInConsultServices = p.ShowInConsultServices,
                        ShowInHomePage = p.ShowInHomePage,
                        ShowInNews = p.ShowInNews,
                        ShowInPayment = p.ShowInPayment,
                        SEO_Title = p.SEO_Title,
                        SEO_Keyword = p.SEO_Keyword,
                        SEO_Description = p.SEO_Description
                    }).ToList();
                }
                return GetAllProduct(currentpage, Convert.ToInt32(ConfigurationManager.AppSettings["itemperpage"]),out totalRecord);
            }
        } 

        public IList<ProductDto> GetTopProducts(int showItems)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                return db.Products.Where(p => p.ShowInHomePage == true).Take(showItems).Select(p => new ProductDto
                {
                    Id = p.Id,
                    Name = p.Name,
                    Price = p.Price,
                    Hotline = p.Hotline,
                    TitleOfImage = p.TitleOfImage,
                    Image = p.Image,
                    Description = p.Description,
                    CategoryId = p.CategoryID,
                    CategoryName = p.Category.Name,
                    CategoryParentId = p.Category.ParentId,
                    CategoryParentName = GetCategory(p.CategoryID, db),
                    ShowInConsultServices = p.ShowInConsultServices,
                    ShowInHomePage = p.ShowInHomePage,
                    ShowInNews = p.ShowInNews,
                    ShowInPayment = p.ShowInPayment,
                    SEO_Title = p.SEO_Title,
                    SEO_Keyword = p.SEO_Keyword,
                    SEO_Description = p.SEO_Description
                }).ToList();
            }
        } 

        public IList<ProductDto> GetProductForSlide(int showItems, bool? isHomepage, bool? isNews, bool? isService, bool? isPayment)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var query = db.Products.ToList();
                if (isHomepage == true)
                    query = query.Where(p => p.ShowInHomePage == true).ToList();
                else if (isNews == true)
                    query = query.Where(p => p.ShowInNews == true).ToList();
                else if (isService == true)
                    query = query.Where(p => p.ShowInConsultServices == true).ToList();
                else if (isPayment == true)
                    query = query.Where(p => p.ShowInPayment == true).ToList();
                if (showItems > 0)
                    query = query.Take(showItems).ToList();
                return query.Select(p => new ProductDto
                                             {
                                                 Id = p.Id,
                                                 Name = p.Name,
                                                 Price = p.Price,
                                                 Hotline = p.Hotline,
                                                 TitleOfImage = p.TitleOfImage,
                                                 Image = p.Image,
                                                 Description = p.Description,
                                                 CategoryId = p.CategoryID,
                                                 CategoryName = p.Category.Name,
                                                 CategoryParentId = p.Category.ParentId,
                                                 CategoryParentName = GetCategory(p.CategoryID, db),
                                                 ShowInConsultServices = p.ShowInConsultServices,
                                                 ShowInHomePage = p.ShowInHomePage,
                                                 ShowInNews = p.ShowInNews,
                                                 ShowInPayment = p.ShowInPayment,
                                                 SEO_Title = p.SEO_Title,
                                                 SEO_Keyword = p.SEO_Keyword,
                                                 SEO_Description = p.SEO_Description
                                             }).ToList();
            }
        } 

        public ProductDto GetProductById(int productId)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                return db.Products.Where(p => p.Id == productId).Select(p => new ProductDto
                {
                    Id = p.Id,
                    Name = p.Name,
                    Price = p.Price,
                    Hotline = p.Hotline,
                    TitleOfImage = p.TitleOfImage,
                    Image = p.Image,
                    Description = p.Description,
                    CategoryId = p.CategoryID,
                    CategoryName = p.Category.Name,
                    CategoryParentId = p.Category.ParentId,
                    CategoryParentName = GetCategory(p.CategoryID, db),
                    ShowInConsultServices = p.ShowInConsultServices,
                    ShowInHomePage = p.ShowInHomePage,
                    ShowInNews = p.ShowInNews,
                    ShowInPayment = p.ShowInPayment,
                    SEO_Title = p.SEO_Title,
                    SEO_Keyword = p.SEO_Keyword,
                    SEO_Description = p.SEO_Description
                }).FirstOrDefault();
            }
        }

        public string GetCategory(int? categoryId, Web_OtoDataContext db)
        {
            var query = db.Categories.FirstOrDefault(c => c.Id == categoryId);
            if (query != null)
            {
                var firstOrDefault = db.Categories.FirstOrDefault(c => c.Id == query.ParentId);
                if (firstOrDefault != null)
                    return firstOrDefault.Name;
            }
            return "";
        }

        public int? GetCategoryByName(string name)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var categories = db.Categories.ToList();
                foreach (var cate in categories)
                {
                    if (CommonHelper.NiceUrl(cate.Name) == name)
                        return cate.Id;
                }
                return null;
            }
        }

        public IList<ProductDto> GetProductDtos(string name = "", int categoryId = 0, int id = 0)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var products = db.Products.Where(p=> (p.Id == id || id == 0) && (p.Name.Contains(name) || string.IsNullOrEmpty(name))
                    && (p.CategoryID == categoryId || categoryId == 0)).ToList();
                return products.Select(p => new ProductDto
                                                {
                                                    Id = p.Id,
                                                    Name = p.Name,
                                                    Price = p.Price,
                                                    TitleOfImage = p.TitleOfImage,
                                                    Image = p.Image,
                                                    Hotline = p.Hotline,
                                                    Description = p.Description,
                                                    CategoryId = p.CategoryID,
                                                    CategoryName = p.Category.Name,
                                                    CategoryParentId = p.Category.ParentId,
                                                    CategoryParentName = GetCategory(p.CategoryID, db),
                                                    ShowInConsultServices = p.ShowInConsultServices,
                                                    ShowInHomePage = p.ShowInHomePage,
                                                    ShowInNews = p.ShowInNews,
                                                    ShowInPayment = p.ShowInPayment,
                                                    SEO_Title = p.SEO_Title,
                                                    SEO_Keyword = p.SEO_Keyword,
                                                    SEO_Description = p.SEO_Description
                                                }).ToList();
            }
        }

        public int Insert(ProductDto productDto)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var result = 1;
                try
                {
                    //Insert product.
                    var product = new Product
                                      {
                                          Name = productDto.Name,
                                          Price = productDto.Price,
                                          Hotline = productDto.Hotline,
                                          Image = productDto.Image,
                                          TitleOfImage = productDto.TitleOfImage,
                                          Description = productDto.Description,
                                          ShowInNews = productDto.ShowInNews,
                                          ShowInConsultServices = productDto.ShowInConsultServices,
                                          ShowInHomePage = productDto.ShowInHomePage,
                                          ShowInPayment = productDto.ShowInPayment,
                                          CategoryID = productDto.CategoryId,
                                          SEO_Title = productDto.SEO_Title,
                                          SEO_Keyword = productDto.SEO_Keyword,
                                          SEO_Description = productDto.SEO_Description
                                      };

                    db.Products.InsertOnSubmit(product);
                    db.SubmitChanges();

                    //Insert relative products.
                    if (productDto.RelativeProducts != null && productDto.RelativeProducts.Any())
                    {
                        var id = db.Products.Max(p => p.Id);
                        foreach (var relativeProduct in productDto.RelativeProducts)
                        {
                            var relaProduct = new RelativeProduct {Id = id, ProductId = relativeProduct.ProductId};
                            db.RelativeProducts.InsertOnSubmit(relaProduct);
                        }

                        db.SubmitChanges();
                    }
                }
                catch (Exception)
                {
                    result = -1;
                }
                return result;
            }
        }

        public int Update(ProductDto productDto, bool isUpdateRelation = true)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var result = 1;
                try
                {
                    //Update product.
                    var product = db.Products.FirstOrDefault(p => p.Id == productDto.Id);
                    if (product != null)
                    {
                        product.Name = productDto.Name;
                        product.Price = productDto.Price;
                        product.Hotline = productDto.Hotline;
                        if (!string.IsNullOrEmpty(productDto.Image))
                            product.Image = productDto.Image;
                        product.TitleOfImage = productDto.TitleOfImage;
                        product.Description = productDto.Description;
                        product.ShowInNews = productDto.ShowInNews;
                        product.ShowInConsultServices = productDto.ShowInConsultServices;
                        product.ShowInHomePage = productDto.ShowInHomePage;
                        product.ShowInPayment = productDto.ShowInPayment;
                        product.CategoryID = productDto.CategoryId;
                        product.SEO_Title = productDto.SEO_Title;
                        product.SEO_Keyword = productDto.SEO_Keyword;
                        product.SEO_Description = productDto.SEO_Description;

                        db.SubmitChanges();
                    }

                    //Update relative product.
                    if (isUpdateRelation && productDto.RelativeProducts != null)
                    {
                        var relaProductDbs = db.RelativeProducts.Where(p => p.Id == productDto.Id).ToList();
                        if (relaProductDbs != null && relaProductDbs.Any())
                        {
                            foreach (var relativeProduct in relaProductDbs)
                            {
                                db.RelativeProducts.DeleteOnSubmit(relativeProduct);
                            }
                            db.SubmitChanges();
                        }

                        if (productDto.RelativeProducts.Any())
                        {
                            foreach (var relativeProduct in productDto.RelativeProducts)
                            {
                                var relaProduct = new RelativeProduct { Id = productDto.Id, ProductId = relativeProduct.ProductId };
                                db.RelativeProducts.InsertOnSubmit(relaProduct);
                            }
                            db.SubmitChanges();
                        }
                    }
                }
                catch (Exception)
                {
                    result = -1;
                }
                return result;
            }
        }

        public int Delete(int id)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var result = 1;
                try
                {
                    //Delete relative product
                    var relaProductDbs = db.RelativeProducts.Where(p => p.ProductId == id || p.Id == id).ToList();
                    if (relaProductDbs.Any())
                    {
                        foreach (var relativeProduct in relaProductDbs)
                        {
                            db.RelativeProducts.DeleteOnSubmit(relativeProduct);
                        }
                        db.SubmitChanges();
                    }

                    //Delete product
                    var product = db.Products.FirstOrDefault(p => p.Id == id);
                    if (product != null)
                    {
                        db.Products.DeleteOnSubmit(product);
                        db.SubmitChanges();
                    }
                }
                catch (Exception)
                {
                    result = -1;
                }
                return result;
            }
        }

        public List<ProductDto> GetRelativeProductDto(int productId)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var productIds = db.RelativeProducts.Where(p => p.Id == productId).Select(p => p.ProductId).ToList();
                var products = db.Products.Where(p => productIds.Contains(p.Id) || productId == 0).ToList();
                return products.Select(p => new ProductDto
                                                {
                                                    Id = p.Id,
                                                    Name = p.Name//,
                                                    //Price = p.Price,
                                                    //Image = p.Image,
                                                    //Hotline = p.Hotline,
                                                    //Description = p.Description,
                                                    //CategoryId = p.CategoryID,
                                                    //CategoryName = p.Category.Name,
                                                    //CategoryParentId = p.Category.ParentId,
                                                    //CategoryParentName = GetCategory(p.CategoryID, db),
                                                    //ShowInConsultServices = p.ShowInConsultServices,
                                                    //ShowInHomePage = p.ShowInHomePage,
                                                    //ShowInNews = p.ShowInNews,
                                                    //ShowInPayment = p.ShowInPayment
                                                }).ToList();
            }
        }

        public List<ProductDto> GetRelativeProductDto(int productId, int? showItems)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var productIds = db.RelativeProducts.Where(p => p.Id == productId).Select(p => p.ProductId).ToList();
                var products = db.Products.Where(p => productIds.Contains(p.Id) || productId == 0).ToList();
                if (showItems != null)
                    products = products.Take(Convert.ToInt32(showItems)).ToList();
                return products.Select(p => new ProductDto
                {
                    Id = p.Id,
                    Name = p.Name,
                    Price = p.Price,
                    Image = p.Image,
                    Hotline = p.Hotline,
                    Description = p.Description,
                    CategoryId = p.CategoryID,
                    CategoryName = p.Category.Name,
                    CategoryParentId = p.Category.ParentId,
                    CategoryParentName = GetCategory(p.CategoryID, db),
                    ShowInConsultServices = p.ShowInConsultServices,
                    ShowInHomePage = p.ShowInHomePage,
                    ShowInNews = p.ShowInNews,
                    ShowInPayment = p.ShowInPayment
                }).ToList();
            }
        }

        public string GetFilePathById(int productId)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                return db.Products.Where(p => p.Id == productId).Select(p => p.Image).FirstOrDefault();
            }
        }

        public IList<ProductDto> SearchProduct(string keyword, int? currentPage, int? itemPerPage, out int totalRecord)
        {
            using (var db = new Web_OtoDataContext(ConfigurationManager.AppSettings["SqlConnect"]))
            {
                var query = db.Products.Where(p => p.Name.ToLower().Contains(keyword.ToLower())).ToList();
                totalRecord = query.Count();
                if (currentPage != null && itemPerPage != null)
                    query = query.Skip((Convert.ToInt32(currentPage) - 1) * Convert.ToInt32(itemPerPage))
                        .Take((Convert.ToInt32(currentPage)) * Convert.ToInt32(itemPerPage))
                        .ToList();
                return query.Select(p => new ProductDto
                {
                    Id = p.Id,
                    Name = p.Name,
                    Price = p.Price,
                    Image = p.Image,
                    Hotline = p.Hotline,
                    TitleOfImage = p.TitleOfImage,
                    Description = p.Description,
                    CategoryId = p.CategoryID,
                    CategoryName = p.Category.Name,
                    CategoryParentId = p.Category.ParentId,
                    CategoryParentName = GetCategory(p.CategoryID, db),
                    ShowInConsultServices = p.ShowInConsultServices,
                    ShowInHomePage = p.ShowInHomePage,
                    ShowInNews = p.ShowInNews,
                    ShowInPayment = p.ShowInPayment
                }).ToList();
            }
        } 

        #endregion
    }
}
